﻿using CallCenter.Data.Base;
using CallCenter.Data.BizUtilities.Base;
using CallCenter.Data.Models;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Text.RegularExpressions;
using System.Threading.Tasks;
namespace CallCenter.Data.BizUtilities
{
    public class SalesLeadUtility : BaseUtility
    {

        public SalesLeadUtility(DataManager manager)
            : base(manager)
        { }


        public ReturnMessage<SalesLeadVM> InsertToSalesLead(SalesLeadVM model)
        {
            var returnMessage = new ReturnMessage<SalesLeadVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            //DateTime NullDate = new DateTime(0001, 1, 1);
            //int DaysClosed;
            //if (model.dateClosed != NullDate)
            //    //if (model.dateOpened <= model.dateClosed && model.dateClosed >= model.startDate)
            //        //if (model.dateOpened <= model.dateClosed)
            //            DaysClosed = (model.dateClosed - model.dateOpened).Days;
            //    //else
            //    //    DaysClosed = 0;
            //else
            //    if (model.dateOpened <= DateTime.Now)
            //    DaysClosed = (DateTime.Now - model.dateOpened).Days;
            //else
            //    DaysClosed = 0;
          
            if (String.IsNullOrEmpty(model.opportunity))
                model.opportunity = "";

            if (model.dateOpened.HasValue && model.dateClosed.HasValue)
            {
                model.daysOpened = (model.dateClosed - model.dateOpened).Value.Days;
                model.daysOpened = (model.daysOpened + 1);
            }

            else if(model.dateOpened.HasValue)
            {
                model.daysOpened = (DateTime.Now - model.dateOpened).Value.Days;
                model.daysOpened = (model.daysOpened + 1);
            }

            
            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";

            if (model.startDate.HasValue)
            {
                // Check if start day is on weekend, update to next coming Monday if so
                if (model.startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                    model.startDate = model.startDate.Value.AddDays(2);
                else if (model.startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                    model.startDate = model.startDate.Value.AddDays(1);
            }

            var id = this.Insert(model.companyId, model.leadContactId, model.loggedOwner, model.contactId, 0, "",
                "", model.productId, model.product, "", model.notes, model.numberOfEmployees, model.annualRevenue, model.monthlyDollar, model.setupDollar, model.dateOpened, model.dateClosed, model.daysOpened, model.opportunity, model.estimatedCost, model.actualCost, model.deposit, model.contractSentDate, model.contractSignedDate, model.depositReceivedDate, model.startDate);
            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;

                returnMessage.Message = "Record has been inserted into database";
                returnMessage.Status = true;
                if(model.depositReceivedDate.HasValue)
                this.Bridge.ProductRequestUtility.Insert(model.companyId, 1,model.productId, 1, 0, 0, 1, model.product, model.contractSentDate, model.contractSignedDate, model.depositReceivedDate, model.deposit, DateTime.Now, DateTime.Now, DateTime.Now, model.estimatedCost, model.actualCost, DateTime.Now, 2, false, model.leadContactId, 0, model.startDate, 0, model.notes, false);
            }
            return returnMessage;
        }

        public ReturnMessage<SalesLeadVM> UpdateSalesLead(SalesLeadVM model)
        {
            var returnMessage = new ReturnMessage<SalesLeadVM>();
            //  if(this.ValidateScratchPadModel(model))
            //   {
            //DateTime NullDate = new DateTime(0001, 1, 1);
            //int DaysClosed;
            //if (model.dateClosed != NullDate)
            //    if (model.dateOpened <= model.dateClosed && model.dateClosed >= model.startDate)
            //        DaysClosed = (model.dateClosed - model.dateOpened).Value.Days;
            //    else
            //        DaysClosed = 0;
            //else
            //    if (model.dateOpened <= DateTime.Now)
            //    DaysClosed = (DateTime.Now - model.dateOpened).Value.Days;
            //else
            //    DaysClosed = 0;

          
            if (String.IsNullOrEmpty(model.opportunity))
                model.opportunity = "";

            if (model.dateOpened.HasValue && model.dateClosed.HasValue)
            {
                model.daysOpened = (model.dateClosed - model.dateOpened).Value.Days;
                model.daysOpened = (model.daysOpened + 1);
            }

            else if (model.dateOpened.HasValue)
            {
                model.daysOpened = (DateTime.Now - model.dateOpened).Value.Days;
                model.daysOpened = (model.daysOpened + 1);
            }


            if (String.IsNullOrEmpty(model.notes))
                model.notes = "";

            if (model.startDate.HasValue)
            {
                // Check if start day is on weekend, update to next coming Monday if so
                if (model.startDate.Value.DayOfWeek == DayOfWeek.Saturday)
                    model.startDate = model.startDate.Value.AddDays(2);
                else if (model.startDate.Value.DayOfWeek == DayOfWeek.Sunday)
                    model.startDate = model.startDate.Value.AddDays(1);
            }



        
            var id = this.Update(model.companyId, model.leadContactId, model.loggedOwner, model.contactId, 0, "", "", model.productId, model.product, "", model.notes, model.numberOfEmployees, model.annualRevenue, model.monthlyDollar,model.setupDollar, model.dateOpened, model.dateClosed, model.daysOpened, model.opportunity, model.estimatedCost, model.actualCost, model.deposit, model.contractSentDate, model.contractSignedDate, model.depositReceivedDate, model.startDate, model.id);

            if (id == -1)
            {
                returnMessage.Message = "We cannot insert data to database";
                returnMessage.Status = false;
            }
            else
            {
                model.id = id;
                returnMessage.Message = "Record has been Updated to  database";
                returnMessage.Status = true;
                if (model.depositReceivedDate.HasValue)
                this.Bridge.ProductRequestUtility.Insert(model.companyId, 1,model.productId, 1, 0, 0, 1, model.product, model.contractSentDate, model.contractSignedDate, model.depositReceivedDate, model.deposit, DateTime.Now, DateTime.Now, DateTime.Now, model.estimatedCost, model.actualCost, DateTime.Now, 2, false, model.leadContactId, 0, model.startDate, 0, model.notes, false);
            }
            return returnMessage;
        }

        public int Insert(int companyId, int leadContactId, string loggedOwner, int contactId, int titleId, string email, string phone,
            int productId, string product, string jobtitle, string notes, int numberOfEmployees, decimal annualRevenue, decimal monthlyDollar, decimal setupDollar, Nullable<DateTime> dateOpened, Nullable<DateTime> dateClosed, int daysOpened,
             string opportunity, decimal estimatedCost, decimal actualCost, decimal deposit, Nullable<DateTime> contractSentDate, Nullable<DateTime> contractSignedDate,
             Nullable<DateTime> depositReceivedDate, Nullable<DateTime> startDate) //insert data to a contact table
        {
            int nRtn = -1;
            string sql = "INSERT INTO ddlSalesLead (companyId,leadContactId, loggedOwner,contactId,titleId,email,phone,productId,product,jobtitle,notes,numberOfEmployees, " +

            " annualRevenue,monthlyDollar, setupDollar, dateOpened,dateClosed,daysOpened, opportunity,estimatedCost,actualCost,deposit,contractSentDate,contractSignedDate, " +
         "  depositReceivedDate,startDate) Values(@companyId, @leadContactId, @loggedOwner, @contactId,@titleId, @email , " +
       "@phone, @productId, @product, @jobtitle, @notes, @numberOfEmployees,@annualRevenue,  @monthlyDollar, @setupDollar, @dateOpened,@dateClosed, @daysOpened,@opportunity, @estimatedCost,@actualCost, @deposit,@contractSentDate, @contractSignedDate,@depositReceivedDate, @startDate) SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@leadContactId", SqlDbType.Int, 100, leadContactId);
                this.Factory.AddParameter(cmd, "@loggedOwner", SqlDbType.NVarChar, 50, loggedOwner);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 100, contactId);

                this.Factory.AddParameter(cmd, "@titleId", SqlDbType.Int, 10, titleId);
                this.Factory.AddParameter(cmd, "@email", SqlDbType.NVarChar, 100, email);

                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 10, phone);
                this.Factory.AddParameter(cmd, "@productId", SqlDbType.Int, 100, productId);

                this.Factory.AddParameter(cmd, "@product", SqlDbType.NVarChar, 100, product);
                this.Factory.AddParameter(cmd, "@jobtitle", SqlDbType.NVarChar, 100, jobtitle);

                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 10, notes);
                this.Factory.AddParameter(cmd, "@numberOfEmployees", SqlDbType.Int, 10, numberOfEmployees);

                this.Factory.AddParameter(cmd, "@annualRevenue", SqlDbType.Decimal, 10, annualRevenue);
                this.Factory.AddParameter(cmd, "@monthlyDollar", SqlDbType.Decimal, 10, monthlyDollar);
                this.Factory.AddParameter(cmd, "@setupDollar", SqlDbType.Decimal, 10, setupDollar);
                //this.Factory.AddParameter(cmd, "@dateOpened", SqlDbType.SmallDateTime, 100, dateOpened);

                //this.Factory.AddParameter(cmd, "@dateClosed", SqlDbType.SmallDateTime, 10, dateClosed);
                this.Factory.AddParameter(cmd, "@daysOpened", SqlDbType.Int, 100, daysOpened);

                this.Factory.AddParameter(cmd, "@opportunity", SqlDbType.NVarChar, 10, opportunity);
                this.Factory.AddParameter(cmd, "@estimatedCost", SqlDbType.Decimal, 100, estimatedCost);

                this.Factory.AddParameter(cmd, "@actualCost", SqlDbType.Decimal, 10, actualCost);
                this.Factory.AddParameter(cmd, "@deposit", SqlDbType.Decimal, 100, deposit);

                     

                if ((contractSentDate == null) || (contractSentDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@contractSentDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@contractSentDate", SqlDbType.DateTime, 10, contractSentDate);


                if ((contractSignedDate == null) || (contractSignedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@contractSignedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@contractSignedDate", SqlDbType.DateTime, 10, contractSignedDate);

                if ((depositReceivedDate == null) || (depositReceivedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@depositReceivedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@depositReceivedDate", SqlDbType.DateTime, 10, depositReceivedDate);

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);

                if ((dateOpened == null) || (dateOpened.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@dateOpened", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@dateOpened", SqlDbType.DateTime, 10, dateOpened);

                if ((dateClosed == null) || (dateClosed.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@dateClosed", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@dateClosed", SqlDbType.DateTime, 10, dateClosed);


                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }



        public int InsertToSalesUpload(int SalesID , string URL, string FileName) //insert data to a contact table
        {
            int nRtn = -1;
            string sql = "INSERT INTO tbl_SalesLeadUpload (SalesLeadId,URL, Name) Values(@SalesLeadId, @URL, @Name) SELECT @pResult =@@IDENTITY";

            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@SalesLeadId", SqlDbType.Int, 10, SalesID);
                this.Factory.AddParameter(cmd, "@URL", SqlDbType.NVarChar, 1000, URL);
                this.Factory.AddParameter(cmd, "@Name", SqlDbType.NVarChar, 50, FileName);
                var param = this.Factory.AddParameter(cmd, "@pResult", System.Data.SqlDbType.NVarChar, 500, null);
                param.Direction = System.Data.ParameterDirection.Output;

                this.Factory.ExecuteNonQuery(cmd);
                nRtn = Convert.ToInt32(param.Value);
            }
            return nRtn;

        }


     
        public int Update(int companyId, int leadContactId, string loggedOwner, int contactId, int titleId, string email, string phone,
            int productId, string product, string jobtitle, string notes, int numberOfEmployees, decimal annualRevenue, decimal monthlyDollar, decimal setupDollar, Nullable<DateTime> dateOpened, Nullable<DateTime> dateClosed, int daysOpened,
             string opportunity, decimal estimatedCost, decimal actualCost, decimal deposit, Nullable<DateTime> contractSentDate, Nullable<DateTime> contractSignedDate,
              Nullable<DateTime> depositReceivedDate, Nullable<DateTime> startDate, int id) //insert data to a title table
        {
            int nRtn = -1;
            string sql = "UPDATE ddlSalesLead SET companyId = @companyId, leadContactId = @leadContactId, loggedOwner = @loggedOwner, contactId = @contactId , " +
             " titleId = @titleId, email = @email, phone = @phone, productId = @productId , product = @product, jobtitle = @jobtitle,notes = @notes,  numberOfEmployees = @numberOfEmployees, annualRevenue = @annualRevenue, monthlyDollar = @monthlyDollar,setupDollar = @setupDollar," +
            " dateOpened = @dateOpened, dateClosed = @dateClosed ,daysOpened = @daysOpened, opportunity = @opportunity,  estimatedCost = @estimatedCost, actualCost = @actualCost ,deposit = @deposit, contractSentDate = @contractSentDate, " +
           " contractSignedDate = @contractSignedDate, depositReceivedDate = @depositReceivedDate, " +
             " startDate = @startDate  WHERE id = @id";




            using (var cmd = this.Factory.GetCommand(sql))
            {

                this.Factory.AddParameter(cmd, "@companyId", SqlDbType.Int, 10, companyId);
                this.Factory.AddParameter(cmd, "@leadContactId", SqlDbType.Int, 100, leadContactId);
                this.Factory.AddParameter(cmd, "@loggedOwner", SqlDbType.NVarChar, 50, loggedOwner);
                this.Factory.AddParameter(cmd, "@contactId", SqlDbType.Int, 100, contactId);

                this.Factory.AddParameter(cmd, "@titleId", SqlDbType.Int, 10, titleId);
                this.Factory.AddParameter(cmd, "@email", SqlDbType.NVarChar, 100, email);

                this.Factory.AddParameter(cmd, "@phone", SqlDbType.NVarChar, 10, phone);
                this.Factory.AddParameter(cmd, "@productId", SqlDbType.Int, 100, productId);

                this.Factory.AddParameter(cmd, "@product", SqlDbType.NVarChar, 100, product);
                this.Factory.AddParameter(cmd, "@jobtitle", SqlDbType.NVarChar, 100, jobtitle);

                this.Factory.AddParameter(cmd, "@notes", SqlDbType.NVarChar, 500, notes);
                this.Factory.AddParameter(cmd, "@numberOfEmployees", SqlDbType.Int, 10, numberOfEmployees);

                this.Factory.AddParameter(cmd, "@daysOpened", SqlDbType.Int, 100, daysOpened);

                this.Factory.AddParameter(cmd, "@annualRevenue", SqlDbType.Decimal, 10, annualRevenue);
                this.Factory.AddParameter(cmd, "@monthlyDollar", SqlDbType.Decimal, 10, monthlyDollar);
                this.Factory.AddParameter(cmd, "@setupDollar", SqlDbType.Decimal, 10, setupDollar);


                this.Factory.AddParameter(cmd, "@opportunity", SqlDbType.NVarChar, 10, opportunity);
                this.Factory.AddParameter(cmd, "@estimatedCost", SqlDbType.Decimal, 100, estimatedCost);

                this.Factory.AddParameter(cmd, "@actualCost", SqlDbType.Decimal, 10, actualCost);
                this.Factory.AddParameter(cmd, "@deposit", SqlDbType.Decimal, 100, deposit);
               
                if ((contractSentDate == null) || (contractSentDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@contractSentDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@contractSentDate", SqlDbType.DateTime, 10, contractSentDate);


                if ((contractSignedDate == null) || (contractSignedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@contractSignedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@contractSignedDate", SqlDbType.DateTime, 10, contractSignedDate);

                if ((depositReceivedDate == null) || (depositReceivedDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@depositReceivedDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@depositReceivedDate", SqlDbType.DateTime, 10, depositReceivedDate);

                if ((startDate == null) || (startDate.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@startDate", SqlDbType.DateTime, 10, startDate);


                if ((dateOpened == null) || (dateOpened.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@dateOpened", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@dateOpened", SqlDbType.DateTime, 10, dateOpened);



                if ((dateClosed == null) || (dateClosed.Equals(DateTime.MinValue)))
                    this.Factory.AddParameter(cmd, "@dateClosed", SqlDbType.DateTime, 10, DBNull.Value);
                else
                    this.Factory.AddParameter(cmd, "@dateClosed", SqlDbType.DateTime, 10, dateClosed);
                this.Factory.AddParameter(cmd, "@id", SqlDbType.Int, 10, id);
                this.Factory.ExecuteNonQuery(cmd);

            }
            nRtn = id;
            return nRtn; //retruning the data stored in the table
        }

        public List<SalesLeadVM> GetSalesGrid()
        {
            string sql = "select Sl.*, cast(Sl.annualRevenue as decimal(18, 3)) as annualRevenue, cast(Sl.monthlyDollar as decimal(18, 3)) as monthlyDollar, cast(Sl.setupDollar as decimal(18, 3)) as setupDollar,  cast(Sl.actualCost as decimal(18, 3)) as actualCost, " +
                 " cast(Sl.estimatedCost as decimal(18, 3)) as estimatedCost, " +
  " cast(Sl.deposit as decimal(18, 3)) as deposit, " +
    " C.name as companycontactname, " +
  " (select top(1) lastName firstName  from ccContacts where id = sl.leadContactId) as leadcontactname, " +
 " (select top(1) name from ccContacts where id = sl.contactId) +'/' + sl.jobtitle + '/' + sl.email + '/' + sl.phone as ContactDetail,  p.product " +
   " from ddlSalesLead as sl " +
  " join ddlCompany as C on sl.companyId = c.id " +
   " join ddlProducts as P on P.id = sl.productId ";
            return this.Factory.ExecuteObjects<SalesLeadVM>(sql);
        }

        public List<SalesLeadVM> GetSalesGridByCompanyId(int CompanyId)
        {
            string sql = "select Sl.*, Sl.jobtitle as code, cast(Sl.annualRevenue as decimal(18, 3)) as annualRevenue,  cast(Sl.actualCost as decimal(18, 3)) as actualCost, " +
                 " cast(Sl.estimatedCost as decimal(18, 3)) as estimatedCost, " +
  " cast(Sl.deposit as decimal(18, 3)) as deposit, " +
    " C.name as companycontactname, " +
" (select top(1)  cc.lastname + ',' + cc.firstname  from ccContacts cc where cc.id = sl.leadContactId) as leadcontactname, " +
 "  (select top(1) cc.lastname + ',' + cc.firstname  from ccContacts cc where cc.id = sl.contactId) as ccontactname, " +
 " (select top(1) name from ccContacts where id = sl.contactId) +'/' + sl.jobtitle + '/' + sl.email + '/' + sl.phone as ContactDetail,  p.product " +
   " from ddlSalesLead as sl " +
  " join ddlCompany as C on sl.companyId = c.id " +
   " join ddlProducts as P on P.id = sl.productId where sl.companyid = " + CompanyId;
            return this.Factory.ExecuteObjects<SalesLeadVM>(sql);
        }


        public List<SalesLeadVM> GetSalesGridByCreateUser()
        {
            string sql = "select Sl.*, Sl.jobtitle as code, cast(Sl.annualRevenue as decimal(18, 3)) as annualRevenue,  cast(Sl.actualCost as decimal(18, 3)) as actualCost, " +
                 " cast(Sl.estimatedCost as decimal(18, 3)) as estimatedCost, " +
  " cast(Sl.deposit as decimal(18, 3)) as deposit, " +
    " C.name as companyname, " +
  " (select top(1)  cc.lastname + ',' + cc.firstname  from ccContacts cc where cc.id = sl.leadContactId) as leadcontactname, " +
 "  (select top(1) cc.lastname + ',' + cc.firstname  from ccContacts cc where cc.id = sl.contactId) as ccontactname, " +
 " (select top(1) name from ccContacts where id = sl.contactId) +'/' + sl.jobtitle + '/' + sl.email + '/' + sl.phone as ContactDetail,  p.product " +
   " from ddlSalesLead as sl " +
  " join ddlCompany as C on sl.companyId = c.id " +
   " join ddlProducts as P on P.id = sl.productId   where sl.dateClosed is null ";
  
            return this.Factory.ExecuteObjects<SalesLeadVM>(sql);
        }



        public List<SalesUploadVM> GetSalesUploadfiles(int SalesId)
        {
            string sql = "select *, Id as id from tbl_SalesLeadUpload where IsActive is null and SalesLeadId = " + SalesId;

            return this.Factory.ExecuteObjects<SalesUploadVM>(sql);
        }



        public void Delete(int titleId) //delete the row of data using the id passed to the function
        {
            string sql = "DELETE FROM ddlSalesLead " +
                  "WHERE ID = @ID ";


            using (var cmd = this.Factory.GetCommand(sql))
            {
                this.Factory.AddParameter(cmd, "@ID", SqlDbType.Int, 10, titleId);
                this.Factory.ExecuteNonQuery(cmd);
            }

        }

        public void DeleteUploadFiles(int salesid , int Id) //delete the row of data using the id passed to the function
        {
            string sql = " update tbl_SalesLeadUpload set IsActive = 0 where SalesLeadId = " + salesid + "  and id = " + Id;
            this.Factory.ExecuteNonQuery(sql);

        }

        public void DeleteAll() //delete all the rows in the data.
        {
            string sql = "DELETE FROM ddlSalesLead ";
            this.Factory.ExecuteTable(sql);

        }

        public List<CompanyContactsViewModel> GetAllContactsCompany()
        {
            string sql = "select A.lastname+','+ A.firstname as name,C.name as companyname from ccContacts A Left join ddlCompanyContacts B on A.id=B.contactId inner join ddlCompany C on c.id=B.companyId where B.IsInActive is null";

            return this.Factory.ExecuteObjects<CompanyContactsViewModel>(sql);
        }

        public List<CompanyContactsViewModel> GetAllCompany(int companyId) //Gets all the data form the contacts table
        {
            string sql = "SELECT compC.companyId, compC.contactId, compC.id, (select name from ddlCompany where id = ct.companyId) as company, " +
                "  ct.lastname+','+ ct.firstname as companycontactname, ct.id as ctId, ct.businessPhone, ct.mobilePhone, ct.email, " +
                " ct.jobTitle,ct.department from ddlCompanyContacts compC " +
                " inner join ddlCompany cp on cp.id = compC.companyId " +
                " inner join ccContacts ct on ct.id = compC.contactId WHERE compC.companyId = " + companyId;
            return this.Factory.ExecuteObjects<CompanyContactsViewModel>(sql);

        }

        public string GetProductNameById(int id)
        {           

            string result = "";
            string sql = string.Format("select product from ddlProducts where id = " + id);

            try
            {
                result = Convert.ToString(this.Factory.ExecuteScalar(sql));
            }
            catch { }
     return result;


        }

        public  string GetJobTitleById(int id)
        {


            string result = "";
            string sql = string.Format("select code from ddlJobTitle where id = " + id);


            try
            {
                result = Convert.ToString(this.Factory.ExecuteScalar(sql));
            }
            catch { }
            return result;

         
       


        }
    }
}


